package com.turing.post.transaction.impl;

import java.util.Date;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.transaction.util.MchHisTradDetail;
import com.turing.post.transaction.util.MchHisTradDetailDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "transaction.impl.MchHisTradDetailDaoImpl")
@Remote
@Local
public class MchHisTradDetailDaoImpl implements MchHisTradDetailDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	@SuppressWarnings("unchecked")
	@Override
	public MchHisTradDetail getMchHisTradDetailByPk(String merchantNo,
			Date tranDate, String tranSeqNo) {
		String sql = "select * from MchHisTradDetail where merchantNo = ?1 and tranDate = ?2 and tranSeqNo = ?3";
		Query query = _em.createNativeQuery(sql, MchHisTradDetail.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, tranSeqNo);
		List<MchHisTradDetail> matdlist = query.getResultList();
		if (matdlist != null && matdlist.size() > 0) {
			return matdlist.get(0);
		} else {
			return null;
		}
	}

	/**
	 * 根据三要素检索商户流水
	 * 
	 * @param startTime
	 *            起始日期
	 * @param endTime
	 *            终止日期
	 * @return 商户交易流水对象列表
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMchHisTradDetailList(PCPSWebInput webInput,
			String startDate, String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issuenameno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select * from ((select TranSeqNo,Amount,FactScorePaidAmount from MchHisTradDetail where  trandate>= to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and trandate<=to_date('"
				+ endDate
				+ "','yyyy-mm-dd') and MerchantNo in(select MerchantNo from Merchant where ChannelMode='03' and IssueCompanyNo=?1)) union all (select TranSeqNo,Amount,FactScorePaidAmount from MchTradDetail where  trandate>= to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and trandate<=to_date('"
				+ endDate
				+ "','yyyy-mm-dd') and MerchantNo in(select MerchantNo from Merchant where ChannelMode='03' and IssueCompanyNo=?1)))";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuenameno);
		List<Object[]> obj_list = query.getResultList();

		return obj_list;
	}

	/**
	 * 根据两要素检索商户单卡交易明细
	 * 
	 * @param CardNo
	 *            卡号
	 * @param startTime
	 *            起始日期
	 * @param endTime
	 *            终止日期
	 * @return 商户单卡交易流水对象列表
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMchCardTranlNoDepositList(PCPSWebInput webInput,
			String startDate, String endDate) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issuenameno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select a.TranDate,a.cardno,c.cardclassicname,a.depositamount,a.paidamount,d.merchantname,a.Transeqno,b.balance"
				+ " from mchaccdetail a, ActivateCard b, CardClassic c, merchant d"
				+ " where a.cardno = b.cardno "
				+ " and b.cardclassicid = c.cardclassicid"
				+ " and a.merchantno = d.merchantno"
				+ " and substr(b.salebranchno,0,6)=?1"
				+ " and a.TranDate>=to_date(?2,'yyyy-mm-dd')"
				+ " and a.TranDate<=to_date(?3,'yyyy-mm-dd')"
				+ " order by a.cardno,a.TranDate";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuenameno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMchCardsTranReport(PCPSWebInput webInput,
			String startTranDate, String endTranDate, String cardNo) {
		String sqlTranDate = "";
		if (null != startTranDate)
			sqlTranDate += " and (m.trandate is null or m.trandate>=to_date('"
					+ startTranDate + " 00:00:00','yyyy-MM-dd hh24:mi:ss')) ";
		if (null != endTranDate) {
			sqlTranDate += " and (m.trandate is null or m.trandate<=to_date('"
					+ endTranDate + " 23:59:59','yyyy-MM-dd hh24:mi:ss')) ";
		}
		String sql = "select m.trantime,m.cardno,(select cardclassicname from cardclassic cc "
				+ "where cardclassicid = (select cardclassicid from activatecard where cardno = m.cardno)) "
				+ "cardclassicname,(select sum(amount) from mchhistraddetail where trancode = '9040' and cardno = m.cardno) "
				+ "deposit,(select sum(amount) from mchhistraddetail where trancode = '9010' and cardno = m.cardno) pain,"
				+ "(select merchantname from merchant where merchantno = m.merchantno) merchantname,m.transeqno from MchHisTradDetail m where 1=1"
				+ sqlTranDate
				+ " and m.cardno='"
				+ cardNo
				+ "' order by m.cardno";

		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 根据要素检索西邮寄卡商户渠道充值明细表
	 * 
	 * @param MerchantNo
	 *            商户号
	 * @param MerchantBranchNo
	 *            商户分支机构号
	 * @param startDate
	 *            起始日期
	 * @param endTime
	 *            终止日期
	 * @return 商户渠道充值明细对象列表
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMchHisTradDetailRechargeList(
			PCPSWebInput webInput, String startDate, String endDate,
			String MerchantNo, String MerchantBranchNo) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate)
				|| PCPSUtil.isEmpty(MerchantNo))
			return null;
		String sql1 = " and a.MrchBrchNo=b.merchantbranchno and  a.TranCode=9040  and (CancelTranSeqNo is null or CancelTranSeqNo=0) and a.merchantno='"
				+ MerchantNo
				+ "'"
				+ " and a.TranDate >=to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and a.TranDate <=to_date('"
				+ endDate
				+ "','yyyy-mm-dd')";
		if (!PCPSUtil.isEmpty(MerchantBranchNo))
			sql1 = sql1 + " and a.MrchBrchNo='" + MerchantBranchNo + "'";
		String sql = " select a.MrchBrchNo,b.merchantbranchname,a.TerminalNo,a.CardNo,a.Amount,a.rescoreremark from MchHisTradDetail a,MerchantBranch b where a.MrchBrchNo=b.merchantbranchno"
				+ sql1
				+ "  union all "
				+ " select a.MrchBrchNo,b.merchantbranchname,a.TerminalNo,a.CardNo,a.Amount,a.rescoreremark from MchTradDetail a ,MerchantBranch b where a.MrchBrchNo=b.merchantbranchno"
				+ sql1 + " order by 1 ";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 根据要素检索西邮寄卡商户渠道充值统计表
	 * 
	 * @param MerchantNo
	 *            商户号
	 * @param startDate
	 *            起始日期
	 * @param endTime
	 *            终止日期
	 * @return 商户渠道充值统计对象列表
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMchHisTradDetailRechargeTotal(
			PCPSWebInput webInput, String startDate, String endDate,
			String MerchantNo) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;

		String sqlwhere = " and  a.TranCode=9040  and (a.CancelTranSeqNo is null or a.CancelTranSeqNo=0) "
				+ " and a.TranDate >=to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and a.TranDate <=to_date('"
				+ endDate
				+ "','yyyy-mm-dd'";
		if (!PCPSUtil.isEmpty(MerchantNo))
			sqlwhere = sqlwhere + " and a.merchantno='" + MerchantNo + "'";
		String sql_1 = "select a.MrchBrchNo,b.merchantbranchname,a.merchantno,c.merchantname,a.Amount from MchHisTradDetail a,MerchantBranch b,Merchant c where a.merchantno=c.merchantno and a.MrchBrchNo=b.merchantbranchno"
				+ sqlwhere
				+ " union all"
				+ " select a.MrchBrchNo,b.merchantbranchname,a.merchantno,c.merchantname,a.Amount from MchTradDetail a,MerchantBranch b,Merchant c where a.merchantno=c.merchantno and a.MrchBrchNo=b.merchantbranchno"
				+ sqlwhere;

		String sql_2 = "select a.MrchBrchNo from MchHisTradDetail a,MerchantBranch b,Merchant c where a.merchantno=c.merchantno and a.MrchBrchNo=b.merchantbranchno"
				+ sqlwhere
				+ " union all"
				+ " select a.MrchBrchNo from MchTradDetail a,MerchantBranch b,Merchant c where a.merchantno=c.merchantno and a.MrchBrchNo=b.merchantbranchno"
				+ sqlwhere;

		String sql = "select MrchBrchNo,merchantbranchname,merchantno,merchantname,count(*),sum(Amount) from ("
				+ sql_1
				+ ") tb where MrchBrchNo in("
				+ sql_2
				+ ") group by MrchBrchNo,merchantbranchname, merchantno,merchantname order by merchantno,MrchBrchNo";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 根据要素检索渠道交易充值轧差表
	 * 
	 * @param MerchantNo
	 *            商户号
	 * @param startDate
	 *            起始日期
	 * @param endTime
	 *            终止日期
	 * @return 商户渠道充值统计对象列表
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMchHisTradDetailAccount(PCPSWebInput webInput,
			String startDate, String endDate, String MerchantNo) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;
		String sql1 = "(select sum(Amount) as Amount1  from MchHisTradDetail where trancode = 9040  and (CancelTranSeqNo is null or CancelTranSeqNo = 0)),";
		String sql2 = "(select sum(Amount) as Amount2,a.merchantno, b.merchantname,a.TranDate  from MchHisTradDetail a,Merchant b where a.trancode = 9010 and a.merchantno = b.merchantno  and (a.CancelTranSeqNo is null or a.CancelTranSeqNo = 0) group by a.merchantno, b.merchantname,a.TranDate),";
		String sql3 = "(select sum(Amount) as Amount3  from MchHisTradDetail where trancode = 9020  and (CancelTranSeqNo is not null ))";

		String sql = "select merchantno,merchantname,case when Amount2 is null then 0 else Amount2 end,"
				+ " case  when Amount1 is null then 0 else Amount1 end,"
				+ " case  when Amount3 is null then 0 else Amount3 end"
				+ " from "
				+ sql1
				+ sql2
				+ sql3
				+ " where TranDate >=to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and TranDate <=to_date('"
				+ endDate + "','yyyy-mm-dd')";
		if (!PCPSUtil.isEmpty(MerchantNo))
			sql = sql + " and merchantno='" + MerchantNo + "'";
		sql = sql + " order by TranDate desc";

		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@Override
	public void insert(MchHisTradDetail mtd) throws PCPSApplicationException {
		GeneralDao.insert(_em, mtd);
	}
}